package com.practice.springboot_01_zwj.scheduled;

// FileImporterService.java 文件导入服务类
import com.practice.springboot_01_zwj.entity.IncomeQuestion;
import com.practice.springboot_01_zwj.entity.ThreeHundred;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Base64;
import java.util.List;

@Service
public class ThreeHundredImportService {

    private static final Logger logger = LoggerFactory.getLogger(ThreeHundredImportService.class);
    private static final int BATCH_SIZE = 2000;
    private static final int REQUIRED_FIELD_COUNT = 20; // 必须X个字段
    private static final SimpleDateFormat[] DATE_FORMATS = {
            new SimpleDateFormat("yyyy/MM/dd"),
            new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"),
            new SimpleDateFormat("dd/MM/yyyy")
    };

    // 字段索引常量（根据实际文件列顺序定义）
    private static final int busiOpportunityNumber = 0;
    private static final int projectName = 1;
    private static final int groupCustomerName = 2;
    private static final int busiOpportunityCode = 3;
    private static final int groupProjectCode = 4;
    private static final int provinceCode = 5;
    private static final int cityName = 6;
    private static final int cityCode = 7;
    private static final int publicCountyCode = 8;
    private static final int industrycounty = 9;
    private static final int zbFlag = 10;
    private static final int zbType = 11;
    private static final int zcFlag = 12;
    private static final int bidAmount = 13;
    private static final int collectionDate = 14;
    private static final int bidOpenDate = 15;
    private static final int zbTime = 16;
    private static final int UniqueProjectInstId = 17;
    private static final int bupMenuNo = 18;
    private static final int tableRecord = 19;


    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 主导入方法
     * @param filePath 文件完整路径
     */
    @Transactional
    public void importLargeFile(String filePath) {
        long startTime = System.currentTimeMillis();
        logger.info("开始导入文件：{}", filePath);

        // ============ 新增删除逻辑 ============
        logger.info("开始删除表中原有数据...");
        long deleteStart = System.currentTimeMillis();
        int deletedRows = jdbcTemplate.update("DELETE FROM gzt_three_hundred");
        logger.info("已删除 {} 条原有数据，耗时：{}ms",
                deletedRows,
                System.currentTimeMillis() - deleteStart);
        // =====================================

        List<ThreeHundred> buffer = new ArrayList<>(BATCH_SIZE);
        int totalLines = 0;
        int successCount = 0;
        boolean isFirstLine = true;

        try (BufferedReader br = new BufferedReader(
                new InputStreamReader(
                        new FileInputStream(filePath),
                        StandardCharsets.UTF_8),
                16384)) {

            String line;
            while ((line = br.readLine()) != null) {
                if(isFirstLine){
                    isFirstLine = false;
                    continue;
                }
                totalLines++;
                try {
                    ThreeHundred entity = parseLine(line, totalLines);
                    if (entity != null) {
                        buffer.add(entity);
                        successCount++;
                    }

                    if (buffer.size() >= BATCH_SIZE) {
                        executeBatchInsert(buffer);
                        buffer.clear();
                    }
                } catch (Exception e) {
                    logger.error("第 {} 行解析失败：{} | 原始行：{}",
                            totalLines, e.getMessage(),
                            truncateString(line, 100));
                }
            }

            if (!buffer.isEmpty()) {
                executeBatchInsert(buffer);
            }

        } catch (Exception e) {
            logger.error("文件读取失败", e);
            throw new RuntimeException("文件导入失败", e);
        }

        logger.info("导入完成，总行数：{}，成功：{}，耗时：{}ms",
                totalLines, successCount,
                System.currentTimeMillis() - startTime);
    }
    // 日期解析方法最终版
    private Date parseDate(String value) {
        if (value == null || value.trim().isEmpty()) {
            return null;
        }

        // 清理数据
        String cleanValue = value.replaceAll("[\"']", "").trim();

        // 特殊处理空值表示
        if (cleanValue.equalsIgnoreCase("null") || cleanValue.equals("-")) {
            return null;
        }

        // 尝试多种日期格式
        for (SimpleDateFormat format : DATE_FORMATS) {
            try {
                format.setLenient(false); // 严格模式
                return new Date(format.parse(cleanValue).getTime());
            } catch (java.text.ParseException e) {
                // 继续尝试下一个格式
            }
        }

        logger.warn("日期解析失败：{}，支持格式：yyyy/MM/dd, yyyy-MM-dd, dd/MM/yyyy", cleanValue);
        return null;
    }
    /**
     * 增强字段提取方法（精准匹配"|"分隔符）
     */
    private List<String> extractFields(String line) {
        List<String> fields = new ArrayList<>(REQUIRED_FIELD_COUNT);

        // 使用竖线分割并保留空值
        String[] parts = line.split("\\|", -1);  // -1参数保留空字段

        // 清理每个字段的引号和空格
        for (String part : parts) {
            String field = part.replaceAll("^\"|\"$", "").trim();
            fields.add(field.isEmpty() ? null : field);
        }

        // 补足缺失字段（根据实际需要）
        while (fields.size() < REQUIRED_FIELD_COUNT) {
            fields.add(null);
        }

        return fields.subList(0, REQUIRED_FIELD_COUNT);
    }

    /**
     * 解析单行数据
     */
    private ThreeHundred parseLine(String line, int lineNumber) {
        if (line.trim().isEmpty()) {
            logger.warn("第 {} 行为空行，已跳过", lineNumber);
            return null;
        }

        List<String> fields = extractFields(line);

        if (fields.size() != REQUIRED_FIELD_COUNT) {
            throw new IllegalArgumentException(
                    String.format("字段数量错误（实际：%d，需要：%d）",
                            fields.size(), REQUIRED_FIELD_COUNT));
        }

        logFieldDetails(lineNumber, fields);

        ThreeHundred entity = new ThreeHundred();
        try {
            // 字符串字段
            entity.setBusiOpportunityNumber(parseString(fields.get(busiOpportunityNumber)));
            entity.setProjectName(parseString(fields.get(projectName)));
//            entity.setGroupCustomerName(parseString(fields.get(groupCustomerName)));
            String groupCustomerNameValue = parseString(fields.get(groupCustomerName));
            if (groupCustomerNameValue != null) {
                groupCustomerNameValue = encryptWithBase64(groupCustomerNameValue);
            }
            entity.setGroupCustomerName(groupCustomerNameValue);
//            entity.setBusiOpportunityCode(parseString(fields.get(busiOpportunityCode)));
            entity.setBusiOpportunityCode(validateStringLength(fields.get(busiOpportunityCode), 2600, "busiOpportunityCode", lineNumber));
            entity.setGroupProjectCode(parseString(fields.get(groupProjectCode)));
            entity.setProvinceCode(parseString(fields.get(provinceCode)));
            entity.setCityName(parseString(fields.get(cityName)));
            entity.setCityCode(parseString(fields.get(cityCode)));
            entity.setPublicCountyCode(parseString(fields.get(publicCountyCode)));
            entity.setIndustrycounty(parseString(fields.get(industrycounty)));
            entity.setZbFlag(parseString(fields.get(zbFlag)));
//            entity.setZbType(parseString(fields.get(zbType)));
            entity.setZbType(validateStringLength(fields.get(zbType), 2600, "zbType", lineNumber));
            entity.setZcFlag(parseString(fields.get(zcFlag)));
            entity.setUniqueProjectInstId(parseString(fields.get(UniqueProjectInstId)));
            entity.setBupMenuNo(parseString(fields.get(bupMenuNo)));
            entity.setTableRecord(parseString(fields.get(tableRecord)));
            // 数值字段（添加空值保护）
            entity.setBidAmount(safeParseDouble(fields.get(bidAmount)));
            //日期字段
            entity.setCollectionDate(parseDate(fields.get(collectionDate)));
            entity.setBidOpenDate(parseDate(fields.get(bidOpenDate)));
            entity.setZbTime(parseDate(fields.get(zbTime)));





        } catch (Exception e) {
            throw new IllegalArgumentException("字段映射失败：" + e.getMessage());
        }
        return entity;
    }

    /**
     * 增强数值转换方法
     */
    private Double safeParseDouble(String value) {
        if (value == null || value.trim().isEmpty()) return null;

        try {
            // 移除数值中的非数字字符（保留小数点和负号）
            String cleanValue = value.replaceAll("[^\\d.-]", "")
                    .replaceAll(",", ""); // 新增去除千分位逗号
            return cleanValue.isEmpty() ? null : Double.parseDouble(cleanValue);
        } catch (NumberFormatException e) {
            logger.warn("数值转换失败：{}", value);
            return null;
        }
    }

    /**
     * 网格ID特殊处理（处理类似 "101110005"I 的情况）
     */
    private Integer safeParseGridId(String value) {
        if (value == null) return null;

        try {
            // 清除尾部多余的"I"字符
            String cleanValue = value.replaceAll("|.*", "")
                    .replaceAll("[^\\d]", "");
            return cleanValue.isEmpty() ? null : Integer.parseInt(cleanValue);
        } catch (Exception e) {
            logger.warn("网格ID转换失败：{}", value);
            return null;
        }
    }

    /**
     * 相似度统一处理方法（处理类似 "0.7575757575757576"I 的情况）
     */
    private Double safeParseSimilarity(String value) {
        if (value == null) return null;

        try {
            // 清除尾部多余的"I"字符和其他非数字符号
            String cleanValue = value.replaceAll("|.*", "")
                    .replaceAll("[^\\d.]", "");
            return cleanValue.isEmpty() ? null : Double.parseDouble(cleanValue);
        } catch (Exception e) {
            logger.warn("相似度转换失败：{}", value);
            return null;
        }
    }

    /**
     * 字符串处理（保留null）
     */
    private String parseString(String value) {
        return (value == null || value.equalsIgnoreCase("null")) ? null : value;
    }

    /**
     * 详细字段日志
     */
    private void logFieldDetails(int lineNumber, List<String> fields) {
        if (logger.isDebugEnabled()) {
            logger.debug("第{}行完整字段解析：", lineNumber);
            for (int i = 0; i < fields.size(); i++) {
                logger.debug("  字段[{}]：{}", i, fields.get(i));
            }
        }
    }
    /**
     * 执行批量插入
     */
    @Transactional(propagation = Propagation.REQUIRED) // 确保在同一个事务中
    void executeBatchInsert(List<ThreeHundred> entities) {
        String sql = "INSERT INTO gzt_three_hundred (busiOpportunityNumber, projectName, groupCustomerName, busiOpportunityCode, groupProjectCode, provinceCode, cityName, cityCode, publicCountyCode, industrycounty, zbFlag, zbType,zcFlag,bidAmount,collectionDate,bidOpenDate,zbTime,UniqueProjectInstId,bupMenuNo,tableRecord) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ThreeHundred entity = entities.get(i);
                int index = 1;

                ps.setString(index++, entity.getBusiOpportunityNumber());
                ps.setString(index++, entity.getProjectName());
                ps.setString(index++, entity.getGroupCustomerName());
                ps.setString(index++, entity.getBusiOpportunityCode());
                ps.setString(index++, entity.getGroupProjectCode());
                ps.setString(index++, entity.getProvinceCode());
                ps.setString(index++, entity.getCityName());
                ps.setString(index++, entity.getCityCode());
                ps.setString(index++, entity.getPublicCountyCode());
                ps.setString(index++, entity.getIndustrycounty());
                ps.setString(index++, entity.getZbFlag());
                ps.setString(index++, entity.getZbType());
                ps.setString(index++, entity.getZcFlag());
                setNullableDouble(ps, index++, entity.getBidAmount());
                setNullableDate(ps, index++, (Date) entity.getCollectionDate());
                setNullableDate(ps, index++, (Date) entity.getBidOpenDate());
                setNullableDate(ps, index++, (Date) entity.getZbTime());
                ps.setString(index++, entity.getUniqueProjectInstId());
                ps.setString(index++, entity.getBupMenuNo());
                ps.setString(index, entity.getTableRecord());
            }

            @Override
            public int getBatchSize() {
                return entities.size();
            }
        });
//        logger.debug("已批量插入 {} 条记录", entities.size());
    }
    // 新增日期设置方法
    private void setNullableDate(PreparedStatement ps, int index, Date value) throws SQLException {
        if (value != null) {
            ps.setDate(index, value);
        } else {
            ps.setNull(index, java.sql.Types.DATE);
        }
    }
    // 辅助方法
    private void setNullableInt(PreparedStatement ps, int index, Integer value) throws SQLException {
        if (value != null) {
            ps.setInt(index, value);
        } else {
            ps.setNull(index, java.sql.Types.INTEGER);
        }
    }

    private void setNullableDouble(PreparedStatement ps, int index, Double value) throws SQLException {
        if (value != null) {
            ps.setDouble(index, value);
        } else {
            ps.setNull(index, java.sql.Types.DOUBLE);
        }
    }

    private String truncateString(String input, int maxLength) {
        return input.length() > maxLength ?
                input.substring(0, maxLength) + "..." : input;
    }
    /**
     * Base64加密方法
     */
    private String encryptWithBase64(String data) {
        if (data == null) {
            return null;
        }
        try {
            return Base64.getEncoder().encodeToString(data.getBytes("UTF-8"));
        } catch (java.io.UnsupportedEncodingException e) {
            // 处理异常，这里简单返回原值
            logger.warn("Base64加密失败，返回原值: {}", data);
            return data;
        }
    }// 新增验证方法
    private String validateStringLength(String value, int maxLength, String fieldName, int lineNumber) {
        String parsed = parseString(value);
        if (parsed != null && parsed.length() > maxLength) {
            logger.warn("第 {} 行 {} 值过长 ({} 字符)，将被截断: {}",
                    lineNumber, fieldName, parsed.length(),
                    truncateString(parsed, 100));
            return parsed.substring(0, maxLength);
        }
        return parsed;
    }

}

